from tqdm import tqdm
import pymysql
import openpyxl
import datetime
from dateutil.relativedelta import relativedelta  # 需要引入新的包


def all_sum():
    sql1 = f"""SELECT COUNT(docID) FROM doc_info;"""  # 总推文
    sql2 = f"""SELECT SUM(like_cnt) FROM doc_info;"""  # 总点赞
    sql3 = f"""SELECT DISTINCT COUNT(addr) FROM user_info;"""  # 总国家
    try:
        # Execute the SQL command
        cursor.execute(sql1)
        l1 = cursor.fetchall()
        cursor.execute(sql2)
        l2 = cursor.fetchall()
        cursor.execute(sql3)
        l3 = cursor.fetchall()
        return l1, l2, l3
    except Exception as e:
        print(e)
        # Rollback in case there is any error回滚
        db.rollback()
        pass


def yuehuanbi():
    # 本月总推文数/点赞数/总国家数   MONTH(CURRENT_DATE)
    sql1 = f"""SELECT DISTINCT
       count(document)
FROM user2doc
where SUBSTRING(date, 1, 4) = YEAR(CURRENT_DATE)
      and SUBSTRING(date, 7, 1) = MONTH(CURRENT_DATE);"""
    sql2 = f"""SELECT DISTINCT
       sum(like_cnt)
FROM user2doc
JOIN doc_info on doc_info.docID = user2doc.documentID
where SUBSTRING(date, 1, 4) = YEAR(CURRENT_DATE)
      and SUBSTRING(date, 7, 1) = MONTH(CURRENT_DATE);"""
    sql3 = f"""SELECT DISTINCT
       count(addr)
FROM user2doc
JOIN user_info on user2doc.userID = user_info.user_id
where SUBSTRING(date, 1, 4) = YEAR(CURRENT_DATE)
      and SUBSTRING(date, 7, 1) = MONTH(CURRENT_DATE);"""

    # 上月总推文数/点赞数/总国家数   MONTH(CURRENT_DATE)-1
    sql4 = f"""SELECT DISTINCT
           count(document)
    FROM user2doc
    where SUBSTRING(date, 1, 4) = YEAR(CURRENT_DATE)
          and SUBSTRING(date, 7, 1) = MONTH(CURRENT_DATE)-1;"""
    sql5 = f"""SELECT DISTINCT
           sum(like_cnt)
    FROM user2doc
    JOIN doc_info on doc_info.docID = user2doc.documentID
    where SUBSTRING(date, 1, 4) = YEAR(CURRENT_DATE)
          and SUBSTRING(date, 7, 1) = MONTH(CURRENT_DATE)-1;"""
    sql6 = f"""SELECT DISTINCT
           count(addr)
    FROM user2doc
    JOIN user_info on user2doc.userID = user_info.user_id
    where SUBSTRING(date, 1, 4) = YEAR(CURRENT_DATE)
          and SUBSTRING(date, 7, 1) = MONTH(CURRENT_DATE)-1;"""
    cursor.execute(sql1)
    l1 = cursor.fetchall()
    cursor.execute(sql2)
    l2 = cursor.fetchall()
    cursor.execute(sql3)
    l3 = cursor.fetchall()
    cursor.execute(sql4)
    l4 = cursor.fetchall()
    cursor.execute(sql5)
    l5 = cursor.fetchall()
    cursor.execute(sql6)
    l6 = cursor.fetchall()
    per_blog = l1[0][0] / l4[0][0] * 100
    per_like = int(l2[0][0]) / int(l5[0][0]) * 100
    per_nation = l3[0][0] / l6[0][0] * 100
    return '{} {} {} {:.2f}% {:.2f}% {:.2f}%'.format(l1[0][0], int(l2[0][0]), l3[0][0], per_blog, per_like, per_nation),


def jihuanbi():
    # 获得本季度第一天
    today = datetime.date.today()
    quarter_start_day = datetime.date(today.year, today.month - (today.month - 1) % 3, 1)
    start_day = quarter_start_day.isoformat()

    # 获得本季度最后一天
    today = datetime.date.today()
    quarter_end_day = datetime.date(today.year, today.month - (today.month - 1) % 3 + 2, 1) + relativedelta(months=1,
                                                                                                            days=-1)
    end_day = quarter_end_day.isoformat()

    # 上季度第一天和最后一天
    las_start_day = start_day + relativedelta(months=-3)
    las_end_day = end_day + relativedelta(months=-3)  # 上两个月，上N个月参数为(months=-N)
    last_start_day = datetime.datetime.strftime(las_start_day, "%Y-%m-%d")
    last_end_day = datetime.datetime.strftime(las_end_day, "%Y-%m-%d")

    # 本季度总推文数
    sql1 = f"""SELECT DISTINCT
            count(document)
            FROM user2doc
            where date between {start_day} and {end_day};"""
    # 本季度总点赞数
    sql2 = f"""SELECT DISTINCT
           sum(like_cnt)
    FROM user2doc
    JOIN doc_info on doc_info.docID = user2doc.documentID
    where date between {start_day} and {end_day};"""
    # 本季度总国家数
    sql3 = f"""SELECT DISTINCT
           count(addr)
    FROM user2doc
    JOIN user_info on user2doc.userID = user_info.user_id
    where date between {start_day} and {end_day};"""
    # 上季度总推文数
    sql4 = f"""SELECT DISTINCT
            count(document)
            FROM user2doc
            where date between {last_start_day} and {last_end_day};"""
    # 上季度总点赞数
    sql5 = f"""SELECT DISTINCT
           sum(like_cnt)
    FROM user2doc
    JOIN doc_info on doc_info.docID = user2doc.documentID
    where date between {last_start_day} and {last_end_day};"""
    # 上季度总国家数
    sql6 = f"""SELECT DISTINCT
           count(addr)
    FROM user2doc
    JOIN user_info on user2doc.userID = user_info.user_id
    where date between {last_start_day} and {last_end_day};"""
    try:
        cursor.execute(sql1)
        l1 = cursor.fetchall()
        cursor.execute(sql2)
        l2 = cursor.fetchall()
        cursor.execute(sql3)
        l3 = cursor.fetchall()
        cursor.execute(sql4)
        l4 = cursor.fetchall()
        cursor.execute(sql5)
        l5 = cursor.fetchall()
        cursor.execute(sql6)
        l6 = cursor.fetchall()
        per_blog = l1[0][0] / l4[0][0] * 100
        per_like = int(l2[0][0]) / int(l5[0][0]) * 100
        per_nation = l3[0][0] / l6[0][0] * 100
        return '{} {} {} {:.2f}% {:.2f}% {:.2f}%'.format(l1[0][0], int(l2[0][0]), l3[0][0], per_blog, per_like,
                                                         per_nation),
    except Exception as e:
        print(e)
        # Rollback in case there is any error回滚
        db.rollback()
        pass


def bannianhuanbi():
    # 获得半年第一天和最后一天
    today = datetime.date.today()
    if today.month <= 6:
        quarter_start_day = datetime.date(today.year, 1, 1)
        quarter_end_day = quarter_start_day + relativedelta(months=6)

    else:
        quarter_start_day = datetime.date(today.year, 7, 1)
        quarter_end_day = quarter_start_day + + relativedelta(months=6)

        start_day = quarter_start_day.isoformat()
        end_day = quarter_end_day.isoformat()
    # 获得上半年的第一天和最后一天
    las_start_day = start_day - relativedelta(months=6)
    las_end_day = end_day - relativedelta(months=6)
    last_start_day = las_start_day.isoformat()
    last_end_day = las_end_day.isoformat()

    # 半年总推文数
    sql1 = f"""SELECT DISTINCT
            count(document)
            FROM user2doc
            where date between {start_day} and {end_day};"""
    # 半年总点赞数
    sql2 = f"""SELECT DISTINCT
           sum(like_cnt)
    FROM user2doc
    JOIN doc_info on doc_info.docID = user2doc.documentID
    where date between {start_day} and {end_day};"""
    # 半年总国家数
    sql3 = f"""SELECT DISTINCT
           count(addr)
    FROM user2doc
    JOIN user_info on user2doc.userID = user_info.user_id
    where date between {start_day} and {end_day};"""
    # 上半年总推文数
    sql4 = f"""SELECT DISTINCT
            count(document)
            FROM user2doc
            where date between {last_start_day} and {last_end_day};"""
    # 上半年总点赞数
    sql5 = f"""SELECT DISTINCT
           sum(like_cnt)
    FROM user2doc
    JOIN doc_info on doc_info.docID = user2doc.documentID
    where date between {last_start_day} and {last_end_day};"""
    # 上半年总国家数
    sql6 = f"""SELECT DISTINCT
           count(addr)
    FROM user2doc
    JOIN user_info on user2doc.userID = user_info.user_id
    where date between {last_start_day} and {last_end_day};"""
    try:
        cursor.execute(sql1)
        l1 = cursor.fetchall()
        cursor.execute(sql2)
        l2 = cursor.fetchall()
        cursor.execute(sql3)
        l3 = cursor.fetchall()
        cursor.execute(sql4)
        l4 = cursor.fetchall()
        cursor.execute(sql5)
        l5 = cursor.fetchall()
        cursor.execute(sql6)
        l6 = cursor.fetchall()
        per_blog = l1[0][0] / l4[0][0] * 100
        per_like = int(l2[0][0]) / int(l5[0][0]) * 100
        per_nation = l3[0][0] / l6[0][0] * 100
        return '{} {} {} {:.2f}% {:.2f}% {:.2f}%'.format(l1[0][0], int(l2[0][0]), l3[0][0], per_blog, per_like,
                                                         per_nation),
    except Exception as e:
        print(e)
        # Rollback in case there is any error回滚
        db.rollback()
        pass


def nianhuanbi():
    # 本年总推文数/点赞数/总国家数   MONTH(CURRENT_DATE)
    sql1 = f"""SELECT DISTINCT
       count(document)
FROM user2doc
where SUBSTRING(date, 1, 4) = YEAR(CURRENT_DATE);"""
    sql2 = f"""SELECT DISTINCT
       sum(like_cnt)
FROM user2doc
JOIN doc_info on doc_info.docID = user2doc.documentID
where SUBSTRING(date, 1, 4) = YEAR(CURRENT_DATE);"""
    sql3 = f"""SELECT DISTINCT
       count(addr)
FROM user2doc
JOIN user_info on user2doc.userID = user_info.user_id
where SUBSTRING(date, 1, 4) = YEAR(CURRENT_DATE);"""

    # 上年总推文数/点赞数/总国家数   MONTH(CURRENT_DATE)-1
    sql4 = f"""SELECT DISTINCT
           count(document)
    FROM user2doc
    where SUBSTRING(date, 1, 4) = YEAR(CURRENT_DATE)-1;"""
    sql5 = f"""SELECT DISTINCT
           sum(like_cnt)
    FROM user2doc
    JOIN doc_info on doc_info.docID = user2doc.documentID
    where SUBSTRING(date, 1, 4) = YEAR(CURRENT_DATE)-1;"""
    sql6 = f"""SELECT DISTINCT
           count(addr)
    FROM user2doc
    JOIN user_info on user2doc.userID = user_info.user_id
    where SUBSTRING(date, 1, 4) = YEAR(CURRENT_DATE)-1;"""
    cursor.execute(sql1)
    l1 = cursor.fetchall()
    cursor.execute(sql2)
    l2 = cursor.fetchall()
    cursor.execute(sql3)
    l3 = cursor.fetchall()
    cursor.execute(sql4)
    l4 = cursor.fetchall()
    cursor.execute(sql5)
    l5 = cursor.fetchall()
    cursor.execute(sql6)
    l6 = cursor.fetchall()
    per_blog = l1[0][0] / l4[0][0] * 100
    per_like = int(l2[0][0]) / int(l5[0][0]) * 100
    per_nation = l3[0][0] / l6[0][0] * 100
    return '{} {} {} {:.2f}% {:.2f}% {:.2f}%'.format(l1[0][0], int(l2[0][0]), l3[0][0], per_blog, per_like, per_nation),


# Open database connection
db = pymysql.connect(
    host='118.89.122.209',
    port=3306,
    user='select_user',
    passwd='1234',
    db='poav_db',
    # cursorclass= pymysql.cursors.DictCursor
    # charset='utf8'  # 字符编码
)
# prepare a cursor object using cursor() method
cursor = db.cursor()

if __name__ == '__main__':
    z, x, c = all_sum()
    print("------总数----------")
    print(z)
    print(x)
    print(c)
    a1, s1, d1, f1, g1, h1 = yuehuanbi()
    a2, s2, d2, f2, g2, h2 = jihuanbi()
    a3, s3, d3, f3, g3, h3 = bannianhuanbi()
    a4, s4, d4, f4, g4, h4 = nianhuanbi()
    print("------总数----------")
    print(a1)
    print(s1)
    print(d1)
    print(f1)
    print(g1)
    print(h1)
